1. Field of the Invention
The invention relates to reducing locking events in Materialized Query Tables (MQTs) in a database management system.
2. Description of the Related Art
Unlike snowflakes, not every query received by a database management system (DBMS) is unique. Often, the same query is submitted repeatedly by various database clients. For example, it may be common to query the number of sales for a company in a particular month. In order to save resources, database systems try to minimize the need to execute common queries from scratch every time. IBM's DB2 database management system uses materialized query tables (MQTs) to save computing resources. MQTs contain pre-aggregated pre-joined data. A query optimizer (such as an SQL optimizer) receives a query and recognizes that the query selects from a base table upon which an MQT is defined. If the MQT is responsive to the query, the SQL optimizer uses the MQT information instead of aggregating data from the base table.
For example, FIG. 1 shows a base table 100 and an associated MQT 120. A query may request information on the number of sales and/or the number of sales people in Ontario in December. A sample query statement may specify:
SELECT   REGION,  MONTH (SALES_DATE),  SUM (SALES)FROM  DB2ADMIN.SALESGROUP BY  REGION,  MONTH(SALES_DATE)The query optimizer recognizes that this data is already pre-aggregated within the MQT 120 and dynamically rewrites the query as follows:
SELECT  REGION,  MONTH,  SALESFROM  DB2ADMIN.MQT_SALES
Thus, using this sort of transformation, the query optimizer can quickly report that for Ontario in December, there were 4 sales. This sales information is retrieved directly from the MQT 120 instead of aggregating this information from the base table 100.
When a new record is inserted into the base table 100 (such as new row 110) the MQT 120 must be updated if it is to continue providing correct values. Currently, if a new row 110 is added to the base table 100, the DBMS locks an associated record in the MQT 120. Thus, when the new row 110 is inserted into the base table 100, the Ontario December row in the MQT 120 is locked. The MQT 120 is then updated to reflect the new value, generating the MQT 130 showing the updated values.
The locking of the MQT 120 can be a severe problem. Every new record inserted into the base table 100 (such as the new row 110) that would generate an update to the MQT record locks the associated MQT 120 record. This lock and release process occurs serially; thus, if twenty new records affect the fifth MQT 120 record (as in the example given above), the DBMS will lock and release the MQT 120 record twenty times as each new record takes its turn updating the MQT 120 record. In short, each base table insert, delete, or update operation has to wait for its turn for the lock in order to update the MQT 120 record.
While locking impacted records in an MQT 120 maintains data accuracy and currency, it also creates a significant bottleneck. This bottleneck is particularly acute where the MQT has a high granularity (that is, a large number of base table 100 rows are summarized in a single MQT row) and where updates occur frequently and quickly. In these situations, locking is more likely and can have serious negative consequences. For example, in a Dynamic Warehouse environment, data enters the base tables while queries are executed against those same base tables. For some database users, this locking problem degrades performance and prevents them from using MQTs beneficially.